from sqlalchemy import Column, String, Integer, Boolean, DateTime, create_engine, ForeignKey, Table
from sqlalchemy.orm import sessionmaker, relationship
from sqlalchemy.ext.declarative import declarative_base
import datetime

# 创建对象的基类:
Base = declarative_base()

# 初始化数据库连接:
engine = create_engine('mysql+pymysql://root:password123@192.168.16.191:3309/MDP', echo=True)

# 创建DBSession类型:
DBSession = sessionmaker(bind=engine)

# user和role关系表
user_roles = Table('user_roles', Base.metadata,
                   Column('user_id', Integer, ForeignKey('users.user_id'), nullable=False, primary_key=True),
                   Column('role_id', Integer, ForeignKey('roles.role_id'), nullable=False, primary_key=True))

role_authors = Table('role_authors', Base.metadata,
                     Column('role_id', Integer, ForeignKey('roles.role_id'), nullable=False, primary_key=True),
                     Column('author_id', Integer, ForeignKey('authors.id'), nullable=False, primary_key=True)
                     )


# 定义Role对象:
class Role(Base):
    # 表的名字:
    __tablename__ = 'roles'
    # 表的结构:
    role_id = Column(Integer, primary_key=True, autoincrement=True)
    name = Column(String(20), nullable=False)
    is_admin = Column(Boolean)
    is_danger = Column(Boolean)
    creation_person = Column(String(10))
    creation_time = Column(DateTime, default=datetime.datetime.now)
    update_person = Column(String(10))
    update_time = Column(DateTime, onupdate=datetime.datetime.now)
    is_enable = Column(Boolean)
    users = relationship("User", secondary=user_roles)
    authors = relationship("Author", secondary=role_authors)


class User(Base):
    # 表的名字:
    __tablename__ = 'users'
    # 表的结构:
    user_id = Column(Integer, primary_key=True, autoincrement=True)
    password = Column(String(255), nullable=False)
    user_name = Column(String(20), nullable=False)  # 用户名
    real_name = Column(String(20), nullable=False)  # 真实姓名
    login_count = Column(Integer, default=0)  # 登录次数
    home_phone = Column(String(20))  # 固定电话
    mobile = Column(String(20))  # 手机号
    gender = Column(Integer)  # 性别
    is_disable = Column(Boolean)  # 是否禁用
    email = Column(String(20))
    register_ip = Column(String(20))  # 注册ip
    register_time = Column(DateTime)
    last_login_ip = Column(String(20))
    last_login_time = Column(DateTime)  # 最后登录时间
    roles = relationship("Role", secondary=user_roles)


class Author(Base):
    __tablename__ = 'authors'

    id = Column(Integer, primary_key=True,  autoincrement=True)
    name = Column(String(50), nullable=False)
    is_menu = Column(Boolean, nullable=False)
    url = Column(String(50), nullable=False)
    code = Column(String(50), nullable=False)
    is_effective = Column(Boolean)
    operate = Column(String(50))
    editor = Column(String(50))
    edit_time = Column(DateTime, onupdate=datetime.datetime.now)
    parent_id = Column(Integer)
    roles = relationship("Role", secondary=role_authors)


# Base.metadata.create_all(engine)

